package fr.inserm.u674.chcdbweb.model.DAO;

import static fr.inserm.u674.chcdbweb.model.DAO.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.jdom.IllegalDataException;

import fr.inserm.u674.chcdbweb.model.DTO.EAVVariable;
import fr.inserm.u674.chcdbweb.model.DTO.Tissue;

public final class EAVVariableDAO {

	// CONSTANTS

	private static final String SQL_LIST = "SELECT * FROM EAV_Variables "
		+ "WHERE deprecatedVariable = 0 ORDER BY variableName ";
	private static final String SQL_LIST_COUNTMOD =
		"SELECT EAV_Variables.creationDate, EAV_Variables.dataType, EAV_Variables.deprecatedVariable, "
			+ "EAV_Variables.description, EAV_Variables.humanReadableName, EAV_Variables.nameAlias, "
			+ "EAV_Variables.variableID, EAV_Variables.variableName , "
			+ "count(DISTINCT modality) AS nMod "
			+ "FROM EAV_Variables LEFT OUTER JOIN EAV_modalities ON (EAV_Variables_variableID = variableID) "
			+ "WHERE deprecatedVariable = 0 "
			+ "GROUP BY EAV_Variables.creationDate, EAV_Variables.dataType, EAV_Variables.deprecatedVariable, "
			+ "EAV_Variables.description, EAV_Variables.humanReadableName, EAV_Variables.nameAlias, "
			+ "EAV_Variables.variableID, EAV_Variables.variableName ORDER BY variableName ";

	private static final String SQL_LIST_DEPRECATED = "SELECT * FROM EAV_Variables "
		+ "ORDER BY variableName";
	private static final String SQL_LIST_NAMES =
		"SELECT variableName FROM EAV_Variables ORDER BY variableName";
	private static final String SQL_LIST_BYVARNAMEMASK =
		"SELECT * FROM EAV_Variables WHERE variableName not LIKE ? AND deprecatedVariable = 0 ";
	private static final String SQL_LIST_BYVARNAMEMASK_WITHDEPRECATED =
		"SELECT * FROM EAV_Variables WHERE variableName not LIKE ? ";

	private static final String SQL_LIST_BYPREFIX =
		"SELECT * FROM EAV_Variables WHERE UPPER(variableName ) LIKE ? "
			+ "AND deprecatedVariable = 0 ";
	private static final String SQL_LIST_BYPREFIX_WITHDEPRECATED =
		"SELECT * FROM EAV_Variables WHERE UPPER(variableName) LIKE ? ";

	private static final String SQL_LIST_BYPREFIXANDMASK =
		"SELECT * FROM EAV_Variables WHERE UPPER(variableName ) LIKE ? AND deprecatedVariable = 0 "
			+ "AND UPPER(variableName) NOT LIKE  ?";
	private static final String SQL_LIST_BYPREFIXANDMASK_WITHDEPRECATED =
		"SELECT * FROM EAV_Variables WHERE UPPER(variableName) LIKE ? "
			+ "AND UPPER(variableName) NOT LIKE  ?";

	private static final String SQL_LIST_UNSETVARIABLE_FORTISSUE =
		"SELECT * FROM EAV_Variables WHERE variableID NOT IN "
			+ "  (SELECT eav_variables_variableID FROM EAV_Modalities"
			+ "	WHERE tissue_chcID = ? AND tissue_tissueTypeID = ?)"
			+ "AND deprecatedVariable = 0 " + "ORDER BY variableName; ";

	private static final String SQL_CREATE =
		"INSERT INTO EAV_Variables "
			+ "(variableName,humanReadableName,dataType,creationDate,description,deprecatedVariable,nameAlias) "
			+ "VALUES (?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE = "UPDATE EAV_Variables " + "SET variableName = ?, "
		+ "humanReadableName = ?, " + "dataType = ?, " + "creationDate = ?, " + "description = ?, "
		+ "deprecatedVariable = ?, " + "nameAlias = ? " + " WHERE variableID = ? ";

	private static final String SQL_FIND_BYNAME =
		"SELECT * FROM EAV_Variables WHERE variableName = ?";

	private static final String SQL_FIND_BYID = "SELECT * FROM EAV_Variables WHERE variableID = ?";

	private static final String SQL_EXISTS_BY_NAME =
		"SELECT COUNT(*) FROM EAV_Variables WHERE variableName = ?";
	private static final String SQL_EXISTS_BY_ID =
		"SELECT COUNT(*) FROM EAV_Variables WHERE variableID = ?";

	private static final String SQL_DELETE = "DELETE FROM EAV_Variables WHERE variableID = ?";

	private static final String SQL_GETMODALITIESINFO =
		"SELECT modality, COUNT(*) as nMod FROM EAV_Variables "
			+ "INNER JOIN Eav_modalities ON (variableID = EAV_Variables_variableID) "
			+ "WHERE variableID = ? " + "GROUP BY modality";

	// FIELDS

	private DAOFactory daoFactory;

	// CONSTRUCTORS

	public EAVVariableDAO(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// ACTIONS

	/**
	 * @param listDeprecatedVariables
	 * @param countModalities
	 * @return
	 * @throws DAOException
	 */
	public List<EAVVariable> list(boolean listDeprecatedVariables, boolean countModalities)
		throws DAOException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<EAVVariable> list = new ArrayList<EAVVariable>();
		try {
			connection = daoFactory.getConnection();
			if (listDeprecatedVariables && !countModalities)
				statement = connection.prepareStatement(SQL_LIST_DEPRECATED);
			else if (!listDeprecatedVariables && !countModalities)
				statement = connection.prepareStatement(SQL_LIST);
			else if (!listDeprecatedVariables && countModalities)
				statement = connection.prepareStatement(SQL_LIST_COUNTMOD);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				list.add(mapEAVVariable(resultSet, countModalities));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return list;
	}

	public List<EAVVariable> listByMask(String variableNameMask, boolean listDeprecatedVariables)
		throws DAOException {
		Object[] values = { variableNameMask };
		ArrayList<EAVVariable> list = new ArrayList<EAVVariable>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			if (listDeprecatedVariables)
				statement =
					prepareStatement(connection, SQL_LIST_BYVARNAMEMASK_WITHDEPRECATED, false,
						values);
			else
				statement = prepareStatement(connection, SQL_LIST_BYVARNAMEMASK, false, values);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				list.add(mapEAVVariable(resultSet, false));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return list;
	}

	public List<EAVVariable> listByPrefix(String prefix, boolean listDeprecatedVariables)
		throws DAOException {
		Object[] values = { prefix.toUpperCase() + "%" };
		ArrayList<EAVVariable> list = new ArrayList<EAVVariable>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			if (listDeprecatedVariables) {
				statement =
					prepareStatement(connection, SQL_LIST_BYPREFIX_WITHDEPRECATED, false, values);
			}
			else {
				statement = prepareStatement(connection, SQL_LIST_BYPREFIX, false, values);
			}
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				list.add(mapEAVVariable(resultSet, false));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return list;
	}

	public List<EAVVariable> listByPrefixAndMask(String prefix, String mask,
		boolean listDeprecatedVariables) throws DAOException {
		Object[] values = { prefix.toUpperCase() + "%", "%" + mask.toUpperCase() + "%" };
		ArrayList<EAVVariable> list = new ArrayList<EAVVariable>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			if (listDeprecatedVariables) {
				statement =
					prepareStatement(connection, SQL_LIST_BYPREFIXANDMASK_WITHDEPRECATED, false,
						values);
			}
			else {
				statement = prepareStatement(connection, SQL_LIST_BYPREFIXANDMASK, false, values);
			}
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				list.add(mapEAVVariable(resultSet, false));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return list;
	}

	public List<EAVVariable> listUnsetVariable(Tissue tissue) throws DAOException {
		Object[] values = { tissue.getChcID(), tissue.getTissueTypeID() };
		ArrayList<EAVVariable> list = new ArrayList<EAVVariable>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement =
				prepareStatement(connection, SQL_LIST_UNSETVARIABLE_FORTISSUE, false, values);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				list.add(mapEAVVariable(resultSet, false));
			}
		}
		catch (SQLException e) {
			throw new DAOConfigurationException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}

		return list;
	}

	public List<String> listNames() throws DAOException {
		ArrayList<String> list = new ArrayList<String>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = connection.prepareStatement(SQL_LIST_NAMES);
			resultSet = statement.executeQuery();
			while (resultSet.next())
				list.add(resultSet.getString(1));
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}

		return list;
	}

	public EAVVariable find(String variableName) throws DAOException {
		return find(SQL_FIND_BYNAME, variableName);
	}

	public EAVVariable find(Long variableID) throws DAOException {
		return find(SQL_FIND_BYID, variableID);
	}

	private EAVVariable find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next())
				return mapEAVVariable(resultSet, false);
			else
				throw new DAOException("No such variable : " + values[0]);
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
	}

	public void create(EAVVariable eavVariable) throws DAOException {

		Object[] values =
			{ eavVariable.getVariableName(), eavVariable.getHumanReadableName(),
				eavVariable.getDataType(), eavVariable.getCreationDate(),
				eavVariable.getDescription(), eavVariable.getDeprecated(),
				eavVariable.getNameAlias() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int affectedRows = 0;
		ResultSet generatedKeys = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_CREATE, true, values);
			affectedRows = preparedStatement.executeUpdate();
			generatedKeys = preparedStatement.getGeneratedKeys();
			if (affectedRows == 0)
				throw new DAOException("EAV Variable creation failed, no rows affected.");
			if (generatedKeys.next())
				eavVariable.setVariableID(generatedKeys.getLong(1));
			else
				throw new DAOException("EAV Variable creation failed, no generated key obtained.");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, generatedKeys);
		}
	}

	/**
	 * 
	 * @param eavVariable
	 * @throws DAOException
	 */
	public void update(EAVVariable eavVariable) throws DAOException {
		if (eavVariable.getVariableID() == null || eavVariable.getVariableName() == null)
			throw new DAOException("EAVVariable is not created yet, name or ID not valid");

		Object[] values =
			{ eavVariable.getVariableName(), eavVariable.getHumanReadableName(),
				eavVariable.getDataType(), eavVariable.getCreationDate(),
				eavVariable.getDescription(), eavVariable.getDeprecated(),
				eavVariable.getNameAlias(), eavVariable.getVariableID() };
		Connection connection = null;
		PreparedStatement statement = null;
		int affectedRows = 0;

		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_UPDATE, false, values);
			affectedRows = statement.executeUpdate();
			if (affectedRows == 0)
				throw new DAOException("EAV Variable update failed, no rows affected.");
			else if (affectedRows > 1)
				throw new DAOException(
					"There was a problem, more than one row was updated, this shouldn't happen.");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
	}

	public boolean existsByName(EAVVariable eavVariable) throws DAOException {
		Object[] values = { eavVariable.getVariableName() };
		boolean exists = false;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_EXISTS_BY_NAME, false, values);
			resultSet = statement.executeQuery();
			resultSet.next();
			if (resultSet.getInt(1) > 0)
				exists = true;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return exists;
	}

	public boolean existsByID(EAVVariable eavVariable) throws DAOException {
		Object[] values = { eavVariable.getVariableID() };
		boolean exists = false;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_EXISTS_BY_ID, false, values);
			resultSet = statement.executeQuery();
			resultSet.next();
			if (resultSet.getInt(1) > 0)
				exists = true;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return exists;
	}

	/**
	 * Deletes the given EAVVariable and returns the number of affected rows
	 * 
	 * @param eavVariable
	 * @return int the number of rows affected by the DELETE query
	 * @throws DAOException
	 */
	public Integer delete(EAVVariable eavVariable) throws DAOException {
		Object[] values = { eavVariable.getVariableID() };
		Connection connection = null;
		PreparedStatement statement = null;
		int affectedRows = 0;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_DELETE, false, values);
			affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("An error ocurred, no rows affected");
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
		return affectedRows;
	}

	/**
	 * This method merges two {@link EAVVariable} in the database, i.e. it takes all the modalities
	 * of the firstVariable and secondVariable, and affects them to a third {@link EAVVariable}.
	 * 
	 * @param firstVariable
	 *            {@link EAVVariable} the variable which will be retained
	 * @param secondVariable
	 *            {@link EAVVariable}
	 * @return
	 * @throws DAOException
	 */
	public EAVVariable merge(EAVVariable firstVariable, EAVVariable secondVariable)
		throws DAOException {
		if (!firstVariable.isSimilarTo(secondVariable)) {
			throw new IllegalArgumentException("The two EAVVariable have conflicting data types !");
		}
		EAVVariable mergingVariable =
			new EAVVariable("MERGE_" + firstVariable.getVariableName() + "_"
				+ secondVariable.getVariableName());
		mergingVariable.setCreationDate(new Date());
		mergingVariable.setDataType(firstVariable.getDataType());
		mergingVariable.setDescription("Merging of variables " + firstVariable + " and "
			+ secondVariable + " done on " + mergingVariable.getCreationDate());
		mergingVariable.setHumanReadableName(firstVariable + " merged with " + secondVariable);
		create(mergingVariable);

		return mergingVariable;
	}

	/**
	 * This method returns a list of all the existing modalities for a given EAVVariable, and the
	 * number of occurrence of each modalities
	 * 
	 * @param eavVariable
	 *            the EAVVariable for which the modalities are to be counted
	 * @return an HashMap<String,Integer> with the value of each modality associated to its number
	 *         of occurrence
	 * @throws DAOException
	 */
	public HashMap<String, Integer> getModalitiesInfo(EAVVariable eavVariable) throws DAOException {
		Object[] values = { eavVariable.getVariableID() };
		HashMap<String, Integer> modalitiesInfo = new HashMap<String, Integer>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_GETMODALITIESINFO, false, values);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				modalitiesInfo.put(resultSet.getString("modality"), resultSet.getInt("nMod"));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return modalitiesInfo;
	}

	// UTILITIES

	protected static EAVVariable mapEAVVariable(ResultSet resultSet, boolean mapModCount)
		throws SQLException {
		Date creationDate = null;
		EAVVariable v;
		try {
			creationDate = resultSet.getDate("creationDate");
		}
		catch (SQLException e) {
			creationDate = new Date(0);
		}
		v =
			new EAVVariable(resultSet.getLong("variableID"), resultSet.getString("variableName"),
				resultSet.getString("humanReadableName"), resultSet.getString("dataType"),
				resultSet.getString("description"), (resultSet.getInt("deprecatedVariable") != 0),
				resultSet.getString("nameAlias"));
		v.setCreationDate(creationDate);
		if (mapModCount)
			v.setnModalities(resultSet.getInt("nMod"));
		return v;
	}
}
